package database.dbRelations;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import objects.Project;
import objects.Student;

import main.Main;
import database.dbObjects.IDBObject;
import database.dbObjects.IDBProject.DBProject;
import database.dbObjects.IDBStudent.DBStudent;

public interface IDBRelProjectStudent extends IDBObject {
	
	public class DBRelProjectStudent extends DBObject {
		
		public final void init() throws Exception {
			
			createTable();
		}
		
		private final void createTable() throws Exception {
			
			String query = "create table RelProjectStudent( " +
	        			   "Project_Name varchar(" + DBProject.maxLengthName + ") not null, " +
	        			   "Student_Email char(" + DBStudent.maxLengthEmail + ") not null, " +
	        			   "foreign key (Project_Name) references Task (Name), " +
	        			   "foreign key (Student_Email) references Student (Email), " +
	        			   "primary key (Project_Name, Student_Email) " +
	        			       "on conflict abort);";
	        
			executeCreation(query);
		}
		
		// TODO eventual triggers
		
		public final boolean add(String projName, String studEmail) throws Exception {
			
			checkConsistance(projName, studEmail);
			
			Vector<Object> v = new Vector<Object>(2);
			v.add(0, projName);
			v.add(1, studEmail);
			
			return super.add("RelProjectStudent", v);
		}
		
		public final boolean delete(String projName, String studEmail) throws Exception {
            
            checkConsistance(projName, studEmail);
            
            Vector<Object> keys = new Vector<Object>(2);
            keys.add(0, projName);
            keys.add(1, studEmail);
            
            return super.delete("RelProjectStudent", keyColumns, keys);
		}
		
	/*	public void printRelProjectStudent(String sql) {
			// TODO nog aan te passen!!
			try {
				Statement stat = Main.connection.createStatement();
				ResultSet rs = stat.executeQuery(sql);
				
				System.out.println("Project naam\t\t|\tStudent ID");
				System.out.println("---------------------------------------------------------------");
				while (rs.next()) {
					System.out.println(rs.getString("Project_Name")+"\t\t|\t"+rs.getString("Student_Email"));
				}
				rs.close();
			} catch(Exception e){
				System.out.println("test-Exception P12!!!");
			}	
		}
	*/	
		
		public final Vector<Project> getProjects(String studEmail) throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from RelProjectStudent join " +
					"(select * from Project natural join Task) " +
					"where ((Student_Email = '" + studEmail + "') and " +
					"(RelProjectStudent.Project_Name = Name));");
			
			Vector<Project> projs = new Vector<Project>();
			int counter = 0;
			while (rs.next()) {
				projs.add(counter, new Project(rs.getString("Name"), 
						rs.getString("Description"), rs.getBoolean("Done"), 
						rs.getInt("Grades"), rs.getString("MoreInformation"), 
						rs.getString("Planning")));
				counter++;
			}
			rs.close();
			return projs;
		}
		
		public final Vector<Student> getStudents(String projName) throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from RelProjectStudent join " +
					"(select * from Student natural join Contact) " +
					"where ((Project_Name = '" + projName + "') and " +
					"(RelProjectStudent.Student_Email = Email));");
			
			Vector<Student> studs = new Vector<Student>();
			int counter = 0;
			while (rs.next()) {
				studs.add(counter, new Student(rs.getString("Student_Email"), 
						rs.getString("Name"), rs.getString("Forename"), 
						rs.getString("PhoneNumber"), rs.getString("MoreInformation"), 
						rs.getString("AlternativeEmail")));
				counter++;
			}
			rs.close();
			return studs;
		}
		
		private final void checkConsistance(String projName, String studEmail) throws Exception {
			
			proj.checkName(projName);
			stud.checkEmail(studEmail);
		}
		
		
		private static final Vector<String> keyColumns = new Vector<String>(2);
		static {
			keyColumns.add(0, "Project_Name");
			keyColumns.add(1, "Student_Email");
		}
		private final DBProject proj = new DBProject();
		private final DBStudent stud = new DBStudent();
	
	}
	
}
